package cn.tianya.irock.data.dalfactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import cn.tianya.irock.enums.SystemExceptionType;
import cn.tianya.irock.exceptions.SystemException;
import cn.tianya.irock.util.TextUtils;

public class SQDatabase {
	protected static Logger logger = Logger.getLogger(SQDatabase.class);
			
	public static final int CONFLICT_NONE = 0;
	private static final String[] CONFLICT_VALUES = new String[] { "",
			" OR ROLLBACK ", " OR ABORT ", " OR FAIL ", " OR IGNORE ",
			" OR REPLACE " };
	
	public Connection getSqlReadConnection() {
		Connection conn = null;
		try {
            Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
        	conn = DriverManager.getConnection("proxool.apollo_read");
        } catch (SQLException ex) {
        	logger.error("SQLException", ex);
        	throw new SystemException(SystemExceptionType.DataProvider, ex.getMessage());
        } catch (ClassNotFoundException ex) {
        	logger.error("SQLException", ex);
        	throw new SystemException(SystemExceptionType.DataProvider, ex.getMessage());
        }

        return conn;
	}
	
	public Connection getSqlWriteConnection() {
		Connection conn = null;
		try {
            Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
        	conn = DriverManager.getConnection("proxool.apollo_write");
        } catch (SQLException ex) {
        	logger.error("SQLException", ex);
        	throw new SystemException(SystemExceptionType.DataProvider, ex.getMessage());
        } catch (ClassNotFoundException ex) {
        	logger.error("SQLException", ex);
        	throw new SystemException(SystemExceptionType.DataProvider, ex.getMessage());
        }

        return conn;
	}

	public ResultSet query(String table, String[] columns, String selection, Object[] selectionArgs, String groupBy, String having, String orderBy) {
        return query(false, table, columns, selection, selectionArgs, groupBy, having, orderBy, null /* limit */);
    }
	
	public ResultSet query(String table, String[] columns, String selection, Object[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        return query(false, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    }
	
	public ResultSet query(boolean distinct, String table, String[] columns, String selection, Object[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        return queryWithFactory(distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    }
	
	public ResultSet queryWithFactory(boolean distinct, String table, String[] columns, String selection, Object[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        String sql = SqlHelp.buildQueryString(distinct, table, columns, selection, groupBy, having, orderBy, limit);
        return rawQueryWithFactory(sql, selectionArgs, findEditTable(table));
    }
	
	public static String findEditTable(String tables) {
		if (!TextUtils.isEmpty(tables)) {
			int spacepos = tables.indexOf(' ');
			int commapos = tables.indexOf(',');

			if (spacepos > 0 && (spacepos < commapos || commapos < 0)) {
				return tables.substring(0, spacepos);
			} else if (commapos > 0 && (commapos < spacepos || spacepos < 0)) {
				return tables.substring(0, commapos);
			}
			return tables;
		} else {
			throw new IllegalStateException("Invalid tables");
		}
	}
	
	public ResultSet rawQueryWithFactory(String sql, Object[] selectionArgs,
			String editTable) {
		Connection conn = getSqlReadConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		
		int numArgs = selectionArgs == null ? 0 : selectionArgs.length;
		try {
			st = conn.prepareStatement(sql);
	        for (int idx = 0; idx < numArgs; idx++) {
	            st.setObject(idx+1, selectionArgs[idx]);
	        }
	        rs = st.executeQuery();
		} catch (SQLException ex) {
			logger.error(ex);
			throw new SystemException(SystemExceptionType.SqlException, ex.getMessage());
		}

		return rs;
	}
	
	public int insert(String table, String nullColumnHack, Map<String, Object> values) {
		return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
	}

	public int insertWithOnConflict(String table, String nullColumnHack, Map<String, Object> initialValues, int conflictAlgorithm) {
		StringBuilder sql = null;
		StringBuilder values = null;
		Set<Map.Entry<String, Object>> entrySet = null;
		Map.Entry<String, Object> entry = null;
		
		sql = new StringBuilder(512);
		sql.append("INSERT");
        sql.append(CONFLICT_VALUES[conflictAlgorithm]);
        sql.append(" INTO ");
        sql.append(table);
        
        values = new StringBuilder(256);
        if (initialValues != null && initialValues.size() > 0) {
            entrySet = initialValues.entrySet();
            Iterator<Map.Entry<String, Object>> entriesIter = entrySet.iterator();
            sql.append('(');

            boolean needSeparator = false;
            while (entriesIter.hasNext()) {
                if (needSeparator) {
                    sql.append(", ");
                    values.append(", ");
                }
                needSeparator = true;
                entry = entriesIter.next();
                sql.append(entry.getKey());
                values.append('?');
            }

            sql.append(')');
        } else {
            sql.append("(" + nullColumnHack + ") ");
            values.append("NULL");
        }

        sql.append(" VALUES(");
        sql.append(values);
        sql.append(");");
        
        Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		int new_id = 0;
		
		conn = getSqlWriteConnection();
		try {
			st = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			
            if (entrySet != null) {
                int size = entrySet.size();
                Iterator<Map.Entry<String, Object>> entriesIter = entrySet.iterator();
                for (int idx = 0; idx < size; idx++) {
                    entry = entriesIter.next();
                    st.setObject(idx+1, entry.getValue());
                }
            }
            st.executeUpdate();
            rs = st.getGeneratedKeys();
        	if (rs.next()) {
        		new_id = rs.getInt(1);
        	}
		} catch (SQLException ex) {
			logger.error(ex);
			throw new SystemException(SystemExceptionType.SqlException, ex.getMessage());
		} finally {
			close(conn, st);
		}
			
		return new_id;
	}
	
	public void close(Connection conn, Statement st, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			}
			catch (Exception e) { }
		}
		
		if (st != null) {
			try {
				st.clearWarnings();
				st.close();
			}
			catch (Exception e) { }
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) { }
		}
	}
	
	public void close(ResultSet rs) {
		Statement st =null;
		Connection conn = null;
		
		try {
			st = rs.getStatement();
		} catch (SQLException e) {
		}
		try {
			conn = st.getConnection();
		} catch (SQLException e) {
		}
		this.close(conn, st, rs);
	}
	
	public void close(Connection conn, ResultSet rs) {
		this.close(conn, null, rs);
	}
	
	public void close(Connection conn, Statement st) {
		this.close(conn, st, null);
	}

	public int update(String table, Map<String, Object> values, List<String> fields, String whereClause, Object[] whereArgs) {
        return updateWithOnConflict(table, values, fields, whereClause, whereArgs, CONFLICT_NONE);
    }
	
	public int updateWithOnConflict(String table, Map<String, Object> values, List<String> fields, String whereClause, Object[] whereArgs, int conflictAlgorithm) {
		StringBuilder sql = null;
		int size = 0;
		
		sql = new StringBuilder(512);
		sql.append("UPDATE ");
		sql.append(CONFLICT_VALUES[conflictAlgorithm]);
		sql.append(table);
		sql.append(" SET ");
		
		Set<Map.Entry<String, Object>> entrySet = null;
        Iterator<Map.Entry<String, Object>> entriesIter = null;
        Map.Entry<String, Object> entry = null;
        
        if (fields != null) {
        	sql.append(StringUtils.join(fields, ","));
        }
        
        if (values != null) {
	        entrySet = values.entrySet();
	        entriesIter = entrySet.iterator();
	        while (entriesIter.hasNext()) {
	            entry = entriesIter.next();
	            sql.append(entry.getKey());
	            sql.append("=?");
	            if (entriesIter.hasNext()) {
	                sql.append(", ");
	            }
	        }
        }
        
        if (!TextUtils.isEmpty(whereClause)) {
            sql.append(" WHERE ");
            sql.append(whereClause);
        }
        
		Connection conn = null;
		PreparedStatement st = null;
		int count = 0;
		
        conn = getSqlWriteConnection();
        try {
			st = conn.prepareStatement(sql.toString());
			
			if (values != null) {
	            size = entrySet.size();
	            entriesIter = entrySet.iterator();
	            for (int idx = 0; idx < size; idx++) {
	                entry = entriesIter.next();
	                st.setObject(idx+1, entry.getValue());
	            }
			}
            if (whereArgs != null) {
	            for (int idx = 0; idx< whereArgs.length; idx++) {
					st.setObject(size + idx + 1, whereArgs[idx]);
	            }
            }
            count = st.executeUpdate();
        } catch (SQLException ex) {
        	logger.error(ex);
        	throw new SystemException(SystemExceptionType.SqlException, ex.getMessage());
        } finally {
        	close(conn, st);
        }
		return count;
	}
	
	public int[] delete(String table, String whereClause, List<Object[]> whereArgLists) {
		Connection conn = null;
		PreparedStatement st = null;
		int[] counts= null;
		
		conn = getSqlWriteConnection();
		try {
			st = conn.prepareStatement("DELETE FROM " + table + (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""));
			for (Object[] whereArgs : whereArgLists) {
				if (whereArgs != null) {
	                for (int idx = 0; idx < whereArgs.length; idx++) {
	                    st.setObject(idx+1, whereArgs[idx]);
	                }
	            }
				st.addBatch();
			}
			counts = st.executeBatch();
		} catch (SQLException ex) {
        	logger.error(ex);
        	throw new SystemException(SystemExceptionType.SqlException, ex.getMessage());
        } finally {
        	close(conn, st);
        }
		return counts;
	}
	
	public int delete(String table, String whereClause, Object[] whereArgs) {
		ArrayList<Object[]> whereArgLists = null;
		int[] counts = null;
		
		whereArgLists = new ArrayList<Object[]>();
		whereArgLists.add(whereArgs);
		counts = delete(table, whereClause, whereArgLists);
		if (counts != null && counts.length > 0) {
			return counts[0];
		} else {
			return 0;
		}
	}
}
